package sh.wn36.vehiclemanage.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;
import sh.wn36.vehiclemanage.entity.InsuranceRecord;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import sh.wn36.vehiclemanage.xzy.InsuranceInfo;
import sh.wn36.vehiclemanage.entity.entity_cjl.reminder.Reminder_insuranceRecordService;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author xk
 * @since 2021-04-26
 */
@Service
public interface InsuranceRecordMapper extends BaseMapper<InsuranceRecord> {

    @Select("SELECT insurance_record.*,car.car_no,current_unit.unit_name,dictionary.text,driver.`name`" +
            " FROM insurance_record" +
            " LEFT JOIN car on car.id=insurance_record.car_id " +
            " LEFT JOIN dictionary on dictionary.id=insurance_record.ins_type" +
            " LEFT JOIN current_unit on current_unit.id=insurance_record.ins_unit " +
            " LEFT JOIN driver on driver.id=insurance_record.operator ${ew.customSqlSegment}")
    IPage<InsuranceInfo> selectPage(Page page, @Param(Constants.WRAPPER) Wrapper<InsuranceInfo> queryWrapper);
    @Select("SELECT c.car_no, i.expire_date FROM insurance_record i LEFT JOIN car c ON i.car_id = c.id")
    List<Reminder_insuranceRecordService> listReminder_insuranceRecordService();

    @Select("SELECT \n" +
            "car.car_no ,\n" +
            "IFNULL(SUM(insurance_record.ins_amount),0) AS car_expenses,\n" +
            "\"保险记录表\" AS '数据来源'\n" +
            "FROM \n" +
            "car LEFT JOIN insurance_record ON insurance_record.car_id = car.id \n" +
            "GROUP BY car.`car_no` " +
            "HAVING car_expenses!=0")
    List<Map<String, Object>> reportList();

    @Select("SELECT \n" +
            "car.car_no ,\n" +
            "IFNULL(SUM(insurance_record.ins_amount),0) AS car_expenses,\n" +
            "\"保险记录表\" AS '数据来源'\n" +
            "FROM \n" +
            "car LEFT JOIN insurance_record ON insurance_record.car_id = car.id \n" +
            "where car.id=#{carId} "+
            "GROUP BY car.`car_no` " +
            "HAVING car_expenses!=0 ")
    List<Map<String, Object>> queryReportByCarId(Integer carId);
}
